package study.top.mydb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.top.common.DBHandler;
import study.top.common.DateTime;
import study.top.jsp.MultipartBox.FileInfo;
import study.top.jsp.MultipartBox;
import study.top.jsp.PageHandler;
import study.top.mydb.items.ReviewItem;

public class Review {
	private static final String TABLE = "review";
	static Logger logger = Logger.getLogger(Review.class);
	DBHandler db = null;		
	public Review() {
		// DB 객체에 대한 참조 연결
		db = DBHandler.getInstance();
	}
	
	/**
	 * 상품별 리뷰 작성
	 * @param title 제목
	 * @param content 내용
	 * @param member_seq 사용자번호
	 * @param product_seq 상품번호
	 * @param imageList 업로드파일
	 * @return
	 */
	public int ReviewWrite(String title, String content,
			int member_seq, int product_seq, ArrayList<FileInfo> imageList){
			
		int seq=-1;
		int review_seq = db.getMax(TABLE, "seq", null) + 1;
		int hit = 0;
		logger.debug("====== method start >> ReviewWrite");

		logger.debug(" -- [PARAMS INFO] --");
		logger.debug("title: " + title);
		logger.debug("content: " + content);
		logger.debug("hit: " + hit);
		logger.debug("member_seq: " + member_seq);
		logger.debug("product_seq: " + product_seq);
			
		String datetime = DateTime.getInstance().getNowDateTimeString();
		
		String mainSql = "insert into review (";
		mainSql += "seq, title, content, hit, member_seq, reg_date, edit_date, product_seq";
		mainSql += ") values (";
		mainSql += "'" + review_seq + "',";
		mainSql += "'" + title.replace("'", "\'") + "',";
		mainSql += "'" + content.replace("'", "\'") + "',";
		mainSql += "'" + hit + "',";
		mainSql += "'" + member_seq + "',";
		mainSql += "'" + datetime + "',";
		mainSql += "'" + datetime + "',";
		mainSql += "'" + product_seq + "')";

		// 여러개의 SQL문이 실행되어야 하므로 트랜잭션 시작
		db.beginTrans();
		
		// 쿼리 수행
		boolean insertOk = db.execSql(mainSql);
		seq = review_seq;;
		
		if (!insertOk) {
			logger.error("REVIEW 게시판 글 저장 실패");
			db.rollback();
			return seq; // -1
		}
		
		// 글 저장이 성공했다면, 방금 저장된 글의 번호 조회
		seq = db.getMax("review", "seq", null);
		
		if(imageList.size() >0) {
		
			String fileSql = "insert into reviewfiles (";
			fileSql += "fileOrgName, fileSaveName, fileDir, fileSize, fileType, ";
			fileSql += "reg_date, edit_date, review_seq";
			fileSql += ") values (";
			fileSql += "'%s', '%s', '%s', %d, '%s', '%s', '%s', %d";
			fileSql += ")";
	
			for (int i = 0; i < imageList.size(); i++) {
				FileInfo item = imageList.get(i);
				boolean fileOk = db.execSql(fileSql, item.getFileOrgName(),
						item.getFileSaveName(), item.getFileDir(),
						item.getFileSize(), item.getFileType(), datetime, datetime,
						seq);
	
				if (!fileOk) {
					// 모든 데이터 저장 취소
					db.rollback();
					return -1;
				}
			}		
		}
		// insert에 대한 실제 데이터 저장
		db.commit();
		logger.debug("====== method end >> ReviewWrite");		
		return seq;				
	}
	
	public ReviewItem read(int seq) {
		ReviewItem item = null;
		
		String hitsql = "UPDATE review SET hit = hit+1 where seq =%d";
		
		/**** 게시물 데이터 불러오기 ****/
		String mainSql = "SELECT r.title, r.content, r.hit, r.imgurl, r.member_seq, m.id, r.product_seq, r.reg_date, r.edit_date ";
		mainSql += "FROM review r inner join member m on r.member_seq=m.seq WHERE r.seq=%d";
		
		boolean result = db.execSql(hitsql, seq);
		if(result){
			db.commit();
		}else{
			db.rollback();
		}
		
		ResultSet rs1 = db.getResult(mainSql, seq);
				
		if (rs1 == null) {
			return null;
		}
		
		// 게시물 데이터를 표현할 변수들
		String title=null, content=null, memberID=null, reg_date=null, edit_date=null, imgurl=null;
		int hit=0 ,member_seq=0, product_seq=0;
		
		// 게시물 데이터의 정상 로딩 여부
		boolean isArticleOk = false;
		
		try {
			// 게시물을 읽기 위해서 첫 위치로 이동 --> 실패한 경우 데이터 없음
			if (rs1.next()) {
				// 게시물 데이터의 변수 저장
				title = rs1.getString(1);
				content = rs1.getString(2);
				hit = rs1.getInt(3);
			//	imgurl = rs1.getString(4);
				member_seq = rs1.getInt(5);
				memberID = rs1.getString(6);
				product_seq = rs1.getInt(7);				
				reg_date = rs1.getString(8);
				edit_date = rs1.getString(9);
				
				// 글은 정상적으로 읽어옴
				isArticleOk = true;
			}
		} catch (SQLException e) {
			// 에러 발생시 에러 정보의 로그 기록
			logger.error(e.getLocalizedMessage());
			logger.error(e.getStackTrace());
		} finally {
			try {
				rs1.close();
			} catch (SQLException e) {
				rs1 = null;
			}
		}
		
		// 글 읽기 실패시 강제 종료
		if (!isArticleOk) {
			return null;
		}
		
		/**** 첨부파일 데이터 불러오기 ****/
		ArrayList<FileInfo> imageList = this.getImageList(seq);		
		
		/**** 게시물 정보 객체 생성 ****/
		item = new ReviewItem(seq, title, content, hit, member_seq, memberID, reg_date, edit_date, product_seq, imgurl, imageList);		
		
		return item;
	}
	
	
	/**
	 * 마이페이지에서 보이는 리뷰목록
	 * @param page
	 * @param member_seq
	 * @return
	 */
	public ArrayList<ReviewItem> mylist(int page, int member_seq) {
		ArrayList<ReviewItem> list = null;	
		String imgurl = null;
		// 리스트에 적용
		String count_table = "(SELECT COUNT(r.seq) ";
		count_table += "FROM review r ";
		count_table += "WHERE r.member_seq="+member_seq;
		//count_table += "WHERE a.subject LIKE '%글% ';
		count_table += " GROUP BY r.seq) ";
		count_table += " AS tbl";
		
		//  전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if (count < 1) {
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, 8, 5);
		
		// 글 목록 조회
		String sql = "SELECT a.seq, a.title, a.content, a.hit, a.imgurl, a.member_seq, m.id, a.reg_date, a.edit_date, a.product_seq ";
		
		sql += "FROM review a ";
		sql += "inner join member m on a.member_seq = m.seq ";
		sql += "where a.member_seq=%d ";
		//sql += "WHERE a.subject LIKE '%글% ';
		sql += "GROUP BY a.seq ";
		sql += "ORDER BY a.seq DESC ";
		sql += "LIMIT %d, %d";
		
		ResultSet rs = db.getResult(sql, member_seq, p.getStartRec(), p.getPageArticleCount());
		
		if (rs == null) {
			return null;
		}
		
		list = new ArrayList<ReviewItem>();
		
		try {
			while (rs.next()) {
				int seq = rs.getInt(1);
				String title = rs.getString(2);
				String content = rs.getString(3);
				int hit = rs.getInt(4);
				//String imgurl = rs.getString(5);
				//int member_seq = rs.getInt(6);
				String memberID = rs.getString(7);
				String reg_date = rs.getString(8);
				String edit_date = rs.getString(9);
				int product_seq = rs.getInt(10);
				
				
				ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
			
				
				ReviewItem item = new ReviewItem(seq, title, content, hit, member_seq, memberID, reg_date, edit_date, product_seq, imgurl, imageList);
				list.add(item);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		return list;
	}
	
	/**
	 * 상품페이지에서 보이는 리뷰목록
	 * @param page
	 * @param product_seq
	 * @return
	 */
	public ArrayList<ReviewItem> list(int page, int product_seq) {
		ArrayList<ReviewItem> list = null;	
		String imgurl = null;
		// 리스트에 적용
		String count_table = "(SELECT seq FROM review) ";
		count_table += " AS tbl";

		//  전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if (count < 1) {
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, 8, 5);
		
		String sql = null;
		ResultSet rs = null;
		
		if(product_seq > 0){
		
			// 글 목록 조회
			sql = "SELECT a.seq, a.title, a.content, a.hit, a.imgurl, a.member_seq, m.id, a.reg_date, a.edit_date ";
			
			sql += "FROM review a ";
			sql += "inner join member m on a.member_seq = m.seq ";
			sql += "where a.product_seq=%d ";		
			//sql += "WHERE a.subject LIKE '%글% ';
			sql += "GROUP BY a.seq ";
			sql += "ORDER BY a.seq DESC ";
			sql += "LIMIT %d, %d";
			
			rs = db.getResult(sql, product_seq, p.getStartRec(), p.getPageArticleCount());
		} else {
			// 글 목록 조회
			sql = "SELECT a.seq, a.title, a.content, a.hit, p.imgurl, a.member_seq, m.id, a.reg_date, a.edit_date ";
			sql += "FROM review a ";
			sql += "inner join member m on a.member_seq = m.seq ";
			sql += "inner join product p on a.product_seq = p.seq ";
			sql += "GROUP BY a.seq ";
			sql += "ORDER BY a.seq DESC ";
			sql += "LIMIT %d, %d";
			
			rs = db.getResult(sql, p.getStartRec(), p.getPageArticleCount());
		}
					
		if (rs == null) {
			return null;
		}
		
		list = new ArrayList<ReviewItem>();
		
		try {
			while (rs.next()) {
				int seq = rs.getInt(1);
				String title = rs.getString(2);
				String content = rs.getString(3);
				int hit = rs.getInt(4);
				imgurl = rs.getString(5);
				int member_seq = rs.getInt(6);
				String memberID = rs.getString(7);
				String reg_date = rs.getString(8);
				String edit_date = rs.getString(9);
				
				
				ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
				
				
				ReviewItem item = new ReviewItem(seq, title, content, hit, member_seq, memberID, reg_date, edit_date, product_seq, imgurl, imageList);
				list.add(item);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		return list;
	}

	public ArrayList<ReviewItem> searchlist(int page, int product_seq, int select, String keyword) {
		logger.debug("page: " + page);
		logger.debug("product_seq: " + product_seq);
		logger.debug("select: " + select);
		logger.debug("keyword: " + keyword);
		
		ArrayList<ReviewItem> list = null;	
		String imgurl = null;
		String search_query = null;
		
		if(select == 1) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.title LIKE '"+keyword+"' ";
				search_query = "r.title LIKE '%%"+keyword+"%%' ";
			}			
		} else if(select == 2) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.content LIKE '"+keyword+"' ";
				search_query = "r.content LIKE '%%"+keyword+"%%' ";
			}			
		}  else if(select == 3) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.content LIKE '"+keyword+"' ";
				search_query = "(r.title LIKE '%%"+keyword+"%%' or r.content LIKE '%%"+keyword+"%%') ";
			}			
		}
						
		// 리스트에 적용
		String count_table = "(SELECT COUNT(r.seq) ";
		count_table += "FROM review r WHERE ";
		count_table += search_query;
		count_table += "GROUP BY r.seq) ";
		count_table += " AS tbl";
		
		//  전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if (count < 1) {
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, 8, 5);
		String sql = null;
		ResultSet rs = null;
		
		if(product_seq >0){
			// 글 목록 조회
			sql = "SELECT r.seq, r.title, r.content, r.hit, r.imgurl, r.member_seq, m.id, r.reg_date, r.edit_date ";		
			sql += "FROM review r ";
			sql += "inner join member m on r.member_seq = m.seq ";			
			sql += "WHERE r.product_seq=%d and ";
			sql += search_query;
			sql += "GROUP BY r.seq ";
			sql += "ORDER BY r.seq DESC ";
			sql += "LIMIT %d, %d";
		
			rs = db.getResult(sql, product_seq, p.getStartRec(), p.getPageArticleCount());
			
		} else {
			// 글 목록 조회
			sql = "SELECT r.seq, r.title, r.content, r.hit, r.imgurl, r.member_seq, m.id, p.imgurl, r.reg_date, r.edit_date ";		
			sql += "FROM review r ";
			sql += "inner join product p on r.product_seq = p.seq ";
			sql += "inner join member m on r.member_seq = m.seq WHERE ";
			sql += search_query;			
			sql += "GROUP BY r.seq ";
			sql += "ORDER BY r.seq DESC ";
			sql += "LIMIT %d, %d";
			rs = db.getResult(sql, p.getStartRec(), p.getPageArticleCount());
		}
		
		if (rs == null) {
			return null;
		}
		
		list = new ArrayList<ReviewItem>();
		
		try {
			while (rs.next()) {
				int seq = rs.getInt(1);
				String title = rs.getString(2);
				String content = rs.getString(3);
				int hit = rs.getInt(4);
				//String imgurl = rs.getString(5);
				int member_seq = rs.getInt(6);
				String memberID = rs.getString(7);
				imgurl = rs.getString(8);
				String reg_date = rs.getString(9);
				String edit_date = rs.getString(10);
				
				
				ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
				
				
				ReviewItem item = new ReviewItem(seq, title, content, hit, member_seq, memberID, reg_date, edit_date, product_seq, imgurl, imageList);
				list.add(item);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		return list;
	}
	
	public ArrayList<ReviewItem> searchMylist(int page, int member_seq, int select, String keyword) {
		logger.debug("page: " + page);
		logger.debug("member_seq: " + member_seq);
		logger.debug("select: " + select);
		logger.debug("keyword: " + keyword);
		
		ArrayList<ReviewItem> list = null;	
		
		String search_query = null;
		String imgurl = null;
		if(select == 1) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.title LIKE '"+keyword+"' ";
				search_query = "WHERE r.member_seq="+member_seq+" and r.title LIKE '%%"+keyword+"%%' ";
			}			
		} else if(select == 2) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.content LIKE '"+keyword+"' ";
				search_query = "WHERE r.member_seq="+member_seq+" and r.content LIKE '%%"+keyword+"%%' ";
			}			
		}  else if(select == 3) {
			if(keyword != null) {
				//keyword = "%" + keyword + "%";
				//search_query = "WHERE r.content LIKE '"+keyword+"' ";
				search_query = "WHERE r.member_seq="+member_seq+" and (r.title LIKE '%%"+keyword+"%%' or r.content LIKE '%%"+keyword+"%%') ";
			}			
		}
						
		// 리스트에 적용
		String count_table = "(SELECT COUNT(r.seq) ";
		count_table += "FROM review r ";
		count_table += search_query;
		
		count_table += " GROUP BY r.seq) ";
		count_table += " AS tbl";
		
		//  전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if (count < 1) {
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, 8, 5);
		
		// 글 목록 조회
		String sql = "SELECT r.seq, r.title, r.content, r.hit, r.imgurl, r.member_seq, m.id, r.reg_date, r.edit_date, r.product_seq ";
		
		sql += "FROM review r ";
		sql += "inner join member m on r.member_seq = m.seq ";
		
		sql += search_query;
		
		sql += "GROUP BY r.seq ";
		sql += "ORDER BY r.seq DESC ";
		sql += "LIMIT %d, %d";
		logger.debug("sql: " + sql);
		logger.debug("p.getStartRec():" + p.getStartRec());
		logger.debug("p.getPageArticleCount(): " + p.getPageArticleCount());
		ResultSet rs = db.getResult(sql, p.getStartRec(), p.getPageArticleCount());
		logger.debug("rs: " + rs);
		
		if (rs == null) {
			return null;
		}
		
		list = new ArrayList<ReviewItem>();
		
		try {
			while (rs.next()) {
				int seq = rs.getInt(1);
				String title = rs.getString(2);
				String content = rs.getString(3);
				int hit = rs.getInt(4);
				//String imgurl = rs.getString(5);
				//int m_seq = rs.getInt(6);
				String memberID = rs.getString(7);
				String reg_date = rs.getString(8);
				String edit_date = rs.getString(9);
				int product_seq = rs.getInt(10);
				
				
				ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
				
				
				ReviewItem item = new ReviewItem(seq, title, content, hit, member_seq, memberID, reg_date, edit_date, product_seq, imgurl, imageList);
				list.add(item);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		return list;
	}
	
	
	public boolean update(int seq, String title, String content, ArrayList<FileInfo> imageList, String[] delete_file, int member_seq) {
		boolean result = false;
		
		// 비밀번호 검사
		int count = db.getCount("review", "seq", "seq=" + seq);
		if (count < 1) {
			return false;
		}
		
		// (1) 삭제할 파일에 대한 리스트를 불러온다.
		ArrayList<FileInfo> delete_file_list = null;
		
		if(delete_file != null){
			String in_cause = "(";
			
			for(int i=0; i<delete_file.length; i++) {
				in_cause += "'" +delete_file[i]+ "'";
				
				if(i+1 < delete_file.length) {
					in_cause += ",";
				}
			}
			in_cause += ")";
			
			// 삭제 대상을 불러올 SQL
			String fileSql = "select fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "from reviewfiles "
					+ "where review_seq=%d and fileSaveName in %s";
			
			ResultSet fileRs = db.getResult(fileSql, seq, in_cause);
			
			//삭제 대상을 만들기 위한 ArrayList 작성
			if(fileRs != null) {
				try {
					delete_file_list = new ArrayList<FileInfo>();
					
					while (fileRs.next()) {
						String fileOrgName = fileRs.getString(1);
						String fileSaveName = fileRs.getString(2);
						String fileDir = fileRs.getString(3);
						int fileSize = fileRs.getInt(4);
						String fileType = fileRs.getString(5);
						
						FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
						delete_file_list.add(info);
					}
				}catch (SQLException e ) {
				}finally {
					if(fileRs != null) {
						try {
							fileRs.close();
						}catch (SQLException e) {
						}
						fileRs = null;
					}
				}
				
				// 게시물 데이터 삭제 처리
				String delSql = "delete from reviewfiles where review_seq=%d and fileSaveName in %s";
				db.execSql(delSql, seq, in_cause);
			}
		}
		
		// (2) 삭제 대상에 대한 처리
		if (delete_file_list != null) {
			MultipartBox mbox = MultipartBox.getInstance();
			mbox.deleteFiles(delete_file_list);
			
		}
		
		// (3) 게시물 정보 갱신
		String sql = "update review set ";
			sql	+= "title='%s', ";		
			sql	+= "content='%s', ";
			sql	+= "edit_date='%s' ";
			sql	+= "where seq=%d and member_seq=%d";
			
			String datetime = DateTime.getInstance().getNowDateTimeString();
			db.execSql(sql, title, content, datetime, seq, member_seq);
		
		
		// (4) 새롭게 업로드 된 파일 정보 처리
			/** (3) 기본 데이터를 참조하는 파일 목록에 대한 SQL 구문 작성 */
			String fileSql = "insert into reviewfiles (";
			fileSql += "fileOrgName, fileSaveName, fileDir, fileSize, fileType, ";
			fileSql += "reg_date, edit_date, review_seq";
			fileSql += ") values (";
			fileSql += "'%s', '%s', '%s', %d, '%s', '%s', '%s', %d";
			fileSql += ")";
			
			// ArryaList에 저장된 파일 데이터의 수 만큼 반복
			for (int i=0; i<imageList.size(); i++) {
				FileInfo item = imageList.get(i);
				db.execSql(fileSql, item.getFileOrgName(), item.getFileSaveName(), item.getFileDir(), 
						item.getFileSize(), item.getFileType(), datetime, datetime, seq);
			}
			
		result = true;
		return result;
	}
	
	public boolean update(int seq, String title, String content, String[] delete_file, int member_seq) {
		boolean result = false;
		
		// 비밀번호 검사
		int count = db.getCount("review", "seq", "seq=" + seq);
		if (count < 1) {
			return false;
		}
		
		// (1) 삭제할 파일에 대한 리스트를 불러온다.
		ArrayList<FileInfo> delete_file_list = null;
		
		if(delete_file != null){
			String in_cause = "(";
			
			for(int i=0; i<delete_file.length; i++) {
				in_cause += "'" +delete_file[i]+ "'";
				
				if(i+1 < delete_file.length) {
					in_cause += ",";
				}
			}
			in_cause += ")";
			
			// 삭제 대상을 불러올 SQL
			String fileSql = "select fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "from reviewfiles "
					+ "where review_seq=%d and fileSaveName in %s";
			
			ResultSet fileRs = db.getResult(fileSql, seq, in_cause);
			
			//삭제 대상을 만들기 위한 ArrayList 작성
			if(fileRs != null) {
				try {
					delete_file_list = new ArrayList<FileInfo>();
					
					while (fileRs.next()) {
						String fileOrgName = fileRs.getString(1);
						String fileSaveName = fileRs.getString(2);
						String fileDir = fileRs.getString(3);
						int fileSize = fileRs.getInt(4);
						String fileType = fileRs.getString(5);
						
						FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
						delete_file_list.add(info);
					}
				}catch (SQLException e ) {
				}finally {
					if(fileRs != null) {
						try {
							fileRs.close();
						}catch (SQLException e) {
						}
						fileRs = null;
					}
				}
				
				// 게시물 데이터 삭제 처리
				String delSql = "delete from reviewfiles where review_seq=%d and fileSaveName in %s";
				db.execSql(delSql, seq, in_cause);
			}
		}
		
		// (2) 삭제 대상에 대한 처리
		if (delete_file_list != null) {
			MultipartBox mbox = MultipartBox.getInstance();
			mbox.deleteFiles(delete_file_list);
			
		}
		
		// (3) 게시물 정보 갱신
		String sql = "update review set ";
			sql	+= "title='%s', ";		
			sql	+= "content='%s', ";
			sql	+= "edit_date='%s' ";
			sql	+= "where seq=%d and member_seq=%d";
			
			String datetime = DateTime.getInstance().getNowDateTimeString();
			db.execSql(sql, title, content, datetime, seq, member_seq);		
			
		result = true;
		return result;
	}
	
	public boolean delete(int seq, int member_seq) {
		boolean result = false;
								
		// 파일업로드 데이터 가져오기
		ArrayList<FileInfo> list = this.getImageList(seq);	
		
		db.beginTrans();
		
		String sql1 = "DELETE FROM reviewfiles WHERE review_seq=%d";
		boolean result1 = db.execSql(sql1, seq);
		
		if (!result1) {
			db.rollback();
			return result;
		}
		
		String sql2 = "DELETE FROM review WHERE seq=%d and member_seq=%d";
		boolean result2 = db.execSql(sql2, seq, member_seq);
		
		if (!result2) {
			db.rollback();
			return result;
		}
		
		// 삭제 쿼리 결과 성공
		result = true;
		db.commit();
		
		// 모든 쿼리가 성공이라면 파일삭제
		MultipartBox mbox = MultipartBox.getInstance();
		mbox.deleteFiles(list);
		
		return result;
	}
	
	public boolean delete(int member_seq) {
		boolean result = false;
								
	
		// 파일업로드 데이터 가져오기
		ArrayList<FileInfo> list = this.getMemImageList(member_seq);	
		
		db.beginTrans();
		
		String sql1 = "DELETE FROM reviewfiles WHERE review_seq in (select seq from review where member_seq=%d)";
		boolean result1 = db.execSql(sql1, member_seq);
		
		if (!result1) {
			db.rollback();
			return result;
		}
		
		String sql2 = "DELETE FROM review WHERE member_seq=%d";
		boolean result2 = db.execSql(sql2, member_seq);
		
		if (!result2) {
			db.rollback();
			return result;
		}
		
		// 삭제 쿼리 결과 성공
		result = true;
		db.commit();
		
		// 모든 쿼리가 성공이라면 파일삭제
		// 모든 쿼리가 성공이라면 파일삭제
		MultipartBox mbox = MultipartBox.getInstance();
		mbox.deleteFiles(list);
		
		
		return result;
	}

	
	private ArrayList<FileInfo> getImageList(int seq) {
		String fileSql = "SELECT fileOrgName, fileSaveName, fileDir, ";
		fileSql += "fileSize, fileType From reviewfiles ";
		fileSql += "WHERE review_seq=%d";
		
		ResultSet rs2 = db.getResult(fileSql, seq);
		
		if (rs2 == null) {
			return null;
		}
		
		ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
		
		// 반복문을 처리하면서 파일목록 생성
		try {
			while (rs2.next()) {
				String fileOrgName = rs2.getString(1);
				String fileSaveName = rs2.getString(2);
				String fileDir = rs2.getString(3);
				int fileSize = rs2.getInt(4);
				String fileType = rs2.getString(5);
				
				FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				imageList.add(info);
			}
		} catch (SQLException e) {
			// 에러 발생시 에러 정보의 로그 기록
			logger.error(e.getLocalizedMessage());
			logger.error(e.getStackTrace());
		} finally {
			try {
				rs2.close();
			} catch (SQLException e) {
				rs2 = null;
			}
		}
		return imageList;
	}
	
	private ArrayList<FileInfo> getMemImageList(int mem_seq) {
		String fileSql = "SELECT fileOrgName, fileSaveName, fileDir, ";
		fileSql += "fileSize, fileType From reviewfiles ";
		fileSql += "WHERE review_seq in (select seq from review where member_seq=%d)";
		
		ResultSet rs2 = db.getResult(fileSql, mem_seq);
		
		if (rs2 == null) {
			return null;
		}
		
		ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
		
		// 반복문을 처리하면서 파일목록 생성
		try {
			while (rs2.next()) {
				String fileOrgName = rs2.getString(1);
				String fileSaveName = rs2.getString(2);
				String fileDir = rs2.getString(3);
				int fileSize = rs2.getInt(4);
				String fileType = rs2.getString(5);
				
				FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				imageList.add(info);
			}
		} catch (SQLException e) {
			// 에러 발생시 에러 정보의 로그 기록
			logger.error(e.getLocalizedMessage());
			logger.error(e.getStackTrace());
		} finally {
			try {
				rs2.close();
			} catch (SQLException e) {
				rs2 = null;
			}
		}
		return imageList;
	}
}
